﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.IO;
using FlexCel.XlsAdapter;
using FlexCel.Core;

using System.Drawing;
using System.Text.RegularExpressions;
using System.Collections.Specialized;
using System.Globalization;

namespace PEC
{
    public class TransferHelper
    {
        public static DataTable PreviewExcelFile(DataTable dt, string filename, string fontCode, string importCode, ref int total, int start = 0, int limit = 0)
        {
            //DataTable dt = new DataTable();
            dt.PrimaryKey = null;
            foreach (DataColumn col in dt.Columns)
            {
                col.AllowDBNull = true;
            }

            if (File.Exists(filename))
            {
                XlsFile xlsFile = new XlsFile(filename);

                //ExcelFile xls;
                // xls.
                List<TXlsNamedRange> arrRange = new List<TXlsNamedRange>();
                int maxCol = 1;
                int maxRow = 1;
                GetDataRange(dt, fontCode, importCode, xlsFile, ref total, start, limit);

                //}
                if (dt.Rows.Count > 0)
                    return dt;
                InitAdvance(dt, xlsFile, arrRange, ref maxCol, ref maxRow);
                total = maxCol * maxRow;
                if (arrRange.Count == 0)
                    return dt;
                GetAdvance(dt, fontCode, xlsFile, arrRange, maxCol, maxRow, start, limit);
            }
            return dt;
        }

        #region Excel to Xml

        public static bool Excel2Xml(string xlsFileName, string xmlFileName, string fontCode, string importCode, ref int total, int start = 0, int limit = 0)
        {
            bool ret = false;
            if (File.Exists(xlsFileName))
            {
                if (File.Exists(xmlFileName)) File.Delete(xmlFileName);

                XlsFile xlsFile = new XlsFile(xlsFileName);
                using (FileStream fs = new FileStream(xmlFileName, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite))
                {
                    using (StreamWriter ws = new StreamWriter(fs))
                    {
                        ret = WriteDataRange2Xml(ws, fontCode, importCode, xlsFile, ref total, start, limit);
                        if (ret == false)
                        {
                            List<TXlsNamedRange> arrRange = new List<TXlsNamedRange>();
                            int maxCol = 1;
                            int maxRow = 1;
                            InitAdvanceXml(xlsFile, arrRange, ref maxCol, ref maxRow, importCode);
                            total = maxCol * maxRow;
                            if (arrRange.Count == 0)
                                ret = false;
                            ret = WriteAdvance2Xml(ws, fontCode, xlsFile, arrRange, maxCol, maxRow, importCode, start, limit);
                        }
                    }
                }

            }
            return ret;

        }
        private static bool WriteDataRange2Xml(StreamWriter ws, string fontCode, string importCode, ExcelFile xlsFile, ref int total, int start = 0, int limit = 0)
        {
            TXlsNamedRange range = xlsFile.GetNamedRange("data_" + importCode, 0);

            if (range == null)
            {
                range = xlsFile.GetNamedRange("data", 0);
            }

            if (range != null)
            {
                xlsFile.ActiveSheet = range.SheetIndex;
                total = range.RowCount - 1;

                if (limit == 0)
                    limit = total;
                ws.Write("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?><rows xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">\n");
                for (int r = 1; r <= limit; r++)
                {
                    if (r + start <= total)
                    {
                        ws.Write("<row>");
                        for (int c = 0; c < range.ColCount; c++)
                        {
                            string name = xlsFile.GetCellValue(range.Top, range.Left + c).ToString();
                            object v = GetValueObj(xlsFile, range.Top + r + start, range.Left + c, fontCode);
                            if (v == null)
                                v = "";
                            if (v is DateTime) ws.Write(string.Format("<{0}>{1:yyyy-MM-dd hh:mm:ss}</{0}>", name, v));
                            else if (v is Double) ws.Write(string.Format("<{0}>{1}</{0}>", name, v));
                            else ws.Write(string.Format("<{0}>{1}</{0}>", name, v.ToString().Trim().Replace("<", "&lt;").Replace(">", "&gt;").Replace("&", "&amp;")));
                        }
                        ws.Write("</row>\n");
                    }
                }
                ws.Write("</rows>");
                return true;
            }
            return false;
        }

        private static void InitAdvanceXml(ExcelFile xlsFile, List<TXlsNamedRange> arrRange, ref int maxCol, ref int maxRow, string importCode)
        {
            for (int i = 0; i < xlsFile.NamedRangeCount; i++)
            {
                string name = xlsFile.GetNamedRange(i + 1).Name;
                if (name.Length > 3 && (name.Substring(0, 3) == "d__" || name.Substring(0, 3) == "f__"))
                {
                    if (name.Contains(String.Format("f__{0}_", importCode)) || name.Contains(String.Format("d__{0}_", importCode)))
                    {
                        string colName = name.Replace(String.Format("f__{0}_", importCode), "").Replace(String.Format("d__{0}_", importCode), "");

                        TXlsNamedRange range = xlsFile.GetNamedRange(i + 1);
                        //if (maxCol < range.ColCount) maxCol = range.ColCount;
                        //if (maxRow < range.RowCount) maxRow = range.RowCount;

                        for (int j = 0; j < arrRange.Count; j++)
                        {
                            if (arrRange[j].Name == "f__" + colName || arrRange[j].Name == "d__" + colName)
                            {
                                arrRange.RemoveAt(j);
                                break;
                            }
                        }
                        if (name.Substring(0, 3) == "d__")
                        {
                            arrRange.Insert(0, range);
                        }
                        else
                        {
                            //if (maxCol < range.ColCount) maxCol = range.ColCount;
                            arrRange.Add(range);
                        }
                    }
                    else
                    {
                        TXlsNamedRange range = xlsFile.GetNamedRange(i + 1);
                        //if (maxCol < range.ColCount) maxCol = range.ColCount;
                        //if (maxRow < range.RowCount) maxRow = range.RowCount;
                        bool flag = true;
                        for (int j = 0; j < arrRange.Count; j++)
                        {
                            if (arrRange[j].Name == String.Format("f__{0}_{1}", importCode, name.Substring(3)) || arrRange[j].Name == String.Format("d__{0}_{1}", importCode, name.Substring(3)))
                            {
                                flag = false;
                                break;
                            }
                        }
                        if (flag)
                        {
                            if (name.Substring(0, 3) == "d__")
                            {
                                arrRange.Insert(0, range);
                            }
                            else
                            {
                                //if (maxCol < range.ColCount) maxCol = range.ColCount;
                                arrRange.Add(range);
                            }
                        }
                    }
                }

            }
            foreach (TXlsNamedRange x in arrRange)
            {
                if (maxCol < x.ColCount) maxCol = x.ColCount;
                if (maxRow < x.RowCount) maxRow = x.RowCount;
            }
        }
        private static bool WriteAdvance2Xml(StreamWriter ws, string fontCode, ExcelFile xlsFile, List<TXlsNamedRange> arrRange, int maxCol, int maxRow, string importCode, int start = 0, int limit = 0)
        {
            int total = maxRow * maxCol;
            if (limit == 0) limit = total;

            int i = start / maxRow;
            int j = start % maxRow;
            bool isBreak = false;
            ws.Write("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?><rows xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">\n");
            for (; i < maxCol; i++)
            {
                if (isBreak) break;
                for (; j < maxRow; j++)
                {
                    if (i * maxRow + j > limit + start)
                    {
                        isBreak = true;
                        break;
                    }
                    int index = 0;
                    ws.Write("<row>");
                    for (; index < arrRange.Count; index++)
                    {
                        xlsFile.ActiveSheet = arrRange[index].SheetIndex;
                        int top = Math.Min(j, arrRange[index].RowCount - 1) + arrRange[index].Top;
                        int left = Math.Min(i, arrRange[index].ColCount - 1) + arrRange[index].Left;
                        string name = GetFieldName(importCode, arrRange[index].Name);
                        if (index == 0)
                        {
                            int atop = top;
                            int aleft = left;
                            if (!xlsFile.CellMergedBounds(top, left).IsOneCell)
                            {
                                atop = xlsFile.CellMergedBounds(top, left).Top;
                                aleft = xlsFile.CellMergedBounds(top, left).Left;
                            }
                            object v = GetValueObj(xlsFile, top, left, fontCode);
                            if (v == null)
                                break;

                            if (v is DateTime) ws.Write(string.Format("<{0}>{1:yyyy-MM-dd hh:mm:ss}</{0}>", name, v));
                            else if (v is Double) ws.Write(string.Format("<{0}>{1}</{0}>", name, v));
                            else ws.Write(string.Format("<{0}>{1}</{0}>", name, v.ToString().Trim().Replace("<", "&lt;").Replace(">", "&gt;").Replace("&", "&amp;")));
                        }
                        else
                        {
                            object v = GetValueObj(xlsFile, top, left, fontCode);
                            if (v == null)
                                v = "";
                            if (v is DateTime) ws.Write(string.Format("<{0}>{1:yyyy-MM-dd hh:mm:ss}</{0}>", name, v));
                            else if (v is Double) ws.Write(string.Format("<{0}>{1}</{0}>", name, v));
                            else ws.Write(string.Format("<{0}>{1}</{0}>", name, v.ToString().Trim().Replace("<", "&lt;").Replace(">", "&gt;").Replace("&", "&amp;")));
                        }

                    }
                    ws.Write("</row>\n");
                }
            }
            ws.Write("</rows>");
            return true;
        }

        #endregion Excel to Xml

        #region Excel to Json

        public static bool Excel2Json(string xlsFileName, string jsonFileName, string fontCode, string importCode, ref int total, int start = 0, int limit = 0)
        {
            bool ret = false;
            if (File.Exists(xlsFileName))
            {
                if (File.Exists(jsonFileName)) File.Delete(jsonFileName);

                XlsFile xlsFile = new XlsFile(xlsFileName);
                using (FileStream fs = new FileStream(jsonFileName, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite))
                {
                    using (StreamWriter ws = new StreamWriter(fs))
                    {
                        ret = WriteDataRange2Json(ws, fontCode, importCode, xlsFile, ref total, start, limit);
                        if (ret == false)
                        {
                            List<TXlsNamedRange> arrRange = new List<TXlsNamedRange>();
                            int maxCol = 1;
                            int maxRow = 1;
                            InitAdvanceJson(xlsFile, arrRange, ref maxCol, ref maxRow, importCode);
                            total = maxCol * maxRow;
                            if (arrRange.Count == 0)
                                ret = false;
                            ret = WriteAdvance2Json(ws, fontCode, xlsFile, arrRange, maxCol, maxRow, importCode, start, limit);
                        }
                    }
                }

            }
            return ret;

        }
        private static bool WriteDataRange2Json(StreamWriter ws, string fontCode, string importCode, ExcelFile xlsFile, ref int total, int start = 0, int limit = 0)
        {
            TXlsNamedRange range = xlsFile.GetNamedRange("data_" + importCode, 0);

            if (range == null)
            {
                range = xlsFile.GetNamedRange("data", 0);
            }

            if (range != null)
            {
                xlsFile.ActiveSheet = range.SheetIndex;
                total = range.RowCount - 1;

                if (limit == 0)
                    limit = total;
                ws.Write("[");
                for (int r = 1; r <= limit; r++)
                {
                    if (r + start <= total)
                    {
                        if (r != 1) ws.Write(",\n");
                        ws.Write("{");
                        for (int c = 0; c < range.ColCount; c++)
                        {
                            if (c != 0) ws.Write(",");
                            string name = xlsFile.GetCellValue(range.Top, range.Left + c).ToString();
                            object v = GetValueObj(xlsFile, range.Top + r + start, range.Left + c, fontCode);
                            if (v == null)
                                v = "";
                            if (v is DateTime) ws.Write(string.Format("\"{0}\":\"{1:yyyy-MM-dd hh:mm:ss}\"", name, v));
                            else if (v is double) ws.Write(string.Format("\"{0}\":{1}", name, v));
                            else ws.Write(string.Format("\"{0}\":\"{1}\"", name, v.ToString().Trim()));
                        }
                        ws.Write("}");
                    }
                }
                ws.Write("]");
                return true;
            }
            return false;
        }

        private static void InitAdvanceJson(ExcelFile xlsFile, List<TXlsNamedRange> arrRange, ref int maxCol, ref int maxRow, string importCode)
        {
            for (int i = 0; i < xlsFile.NamedRangeCount; i++)
            {
                string name = xlsFile.GetNamedRange(i + 1).Name;
                if (name.Length > 3 && (name.Substring(0, 3) == "d__" || name.Substring(0, 3) == "f__"))
                {
                    if (name.Contains(String.Format("f__{0}_", importCode)) || name.Contains(String.Format("d__{0}_", importCode)))
                    {
                        string colName = name.Replace(String.Format("f__{0}_", importCode), "").Replace(String.Format("d__{0}_", importCode), "");

                        TXlsNamedRange range = xlsFile.GetNamedRange(i + 1);
                        //if (maxCol < range.ColCount) maxCol = range.ColCount;
                        //if (maxRow < range.RowCount) maxRow = range.RowCount;

                        for (int j = 0; j < arrRange.Count; j++)
                        {
                            if (arrRange[j].Name == "f__" + colName || arrRange[j].Name == "d__" + colName)
                            {
                                arrRange.RemoveAt(j);
                                break;
                            }
                        }
                        if (name.Substring(0, 3) == "d__")
                        {
                            arrRange.Insert(0, range);
                        }
                        else
                        {
                            //if (maxCol < range.ColCount) maxCol = range.ColCount;
                            arrRange.Add(range);
                        }
                    }
                    else
                    {
                        TXlsNamedRange range = xlsFile.GetNamedRange(i + 1);
                        //if (maxCol < range.ColCount) maxCol = range.ColCount;
                        //if (maxRow < range.RowCount) maxRow = range.RowCount;
                        bool flag = true;
                        for (int j = 0; j < arrRange.Count; j++)
                        {
                            if (arrRange[j].Name == String.Format("f__{0}_{1}", importCode, name.Substring(3)) || arrRange[j].Name == String.Format("d__{0}_{1}", importCode, name.Substring(3)))
                            {
                                flag = false;
                                break;
                            }
                        }
                        if (flag)
                        {
                            if (name.Substring(0, 3) == "d__")
                            {
                                arrRange.Insert(0, range);
                            }
                            else
                            {
                                //if (maxCol < range.ColCount) maxCol = range.ColCount;
                                arrRange.Add(range);
                            }
                        }
                    }
                }

            }
            foreach (TXlsNamedRange x in arrRange)
            {
                if (maxCol < x.ColCount) maxCol = x.ColCount;
                if (maxRow < x.RowCount) maxRow = x.RowCount;
            }
        }
        private static bool WriteAdvance2Json(StreamWriter ws, string fontCode, ExcelFile xlsFile, List<TXlsNamedRange> arrRange, int maxCol, int maxRow, string importCode, int start = 0, int limit = 0)
        {
            int total = maxRow * maxCol;
            if (limit == 0) limit = total;

            int i = start / maxRow;
            int j = start % maxRow;
            bool isBreak = false;
            ws.Write("[");
            for (; i < maxCol; i++)
            {
                if (isBreak) break;
                for (; j < maxRow; j++)
                {
                    if (i * maxRow + j > limit + start)
                    {
                        isBreak = true;
                        break;
                    }
                    int index = 0;
                    if (i == start / maxRow && j == start % maxRow) ws.Write(",\n");

                    ws.Write("{");

                    for (; index < arrRange.Count; index++)
                    {
                        xlsFile.ActiveSheet = arrRange[index].SheetIndex;
                        int top = Math.Min(j, arrRange[index].RowCount - 1) + arrRange[index].Top;
                        int left = Math.Min(i, arrRange[index].ColCount - 1) + arrRange[index].Left;
                        string name = GetFieldName(importCode, arrRange[index].Name);
                        if (index == 0)
                        {
                            int atop = top;
                            int aleft = left;
                            if (!xlsFile.CellMergedBounds(top, left).IsOneCell)
                            {
                                atop = xlsFile.CellMergedBounds(top, left).Top;
                                aleft = xlsFile.CellMergedBounds(top, left).Left;
                            }
                            object v = GetValueObj(xlsFile, top, left, fontCode);
                            if (v == null)
                                break;

                            if (v is DateTime) ws.Write(string.Format("\"{0}\":\"{1:yyyy-MM-dd hh:mm:ss}\"", name, v));
                            else if (v is double) ws.Write(string.Format("\"{0}\":{1}", name, v));
                            else ws.Write(string.Format("\"{0}\":\"{1}\"", name, v.ToString().Trim()));
                        }
                        else
                        {
                            object v = GetValueObj(xlsFile, top, left, fontCode);
                            if (v == null)
                                v = "";
                            if (v is DateTime) ws.Write(string.Format("\"{0}\":\"{1:yyyy-MM-dd hh:mm:ss}\"", name, v));
                            else if (v is double) ws.Write(string.Format("\"{0}\":{1}", name, v));
                            else ws.Write(string.Format("\"{0}\":\"{1}\"", name, v.ToString().Trim()));
                        }

                    }
                    ws.Write("}");
                }
            }
            ws.Write("]");
            return true;
        }

        #endregion Excel to Json

        #region Excel to PHP

        public static bool Excel2PHP(string xlsFileName, string phpFileName, string fontCode, string importCode, ref int total, int start = 0, int limit = 0)
        {
            bool ret = false;
            if (File.Exists(xlsFileName))
            {
                if (File.Exists(phpFileName)) File.Delete(phpFileName);

                XlsFile xlsFile = new XlsFile(xlsFileName);
                using (FileStream fs = new FileStream(phpFileName, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite))
                {
                    using (StreamWriter ws = new StreamWriter(fs))
                    {
                        ret = WriteDataRange2PHP(ws, fontCode, importCode, xlsFile, ref total, start, limit);
                        if (ret == false)
                        {
                            List<TXlsNamedRange> arrRange = new List<TXlsNamedRange>();
                            int maxCol = 1;
                            int maxRow = 1;
                            InitAdvancePHP(xlsFile, arrRange, ref maxCol, ref maxRow, importCode);
                            total = maxCol * maxRow;
                            if (arrRange.Count == 0)
                                ret = false;
                            ret = WriteAdvance2PHP(ws, fontCode, xlsFile, arrRange, maxCol, maxRow, importCode, start, limit);
                        }
                    }
                }

            }
            return ret;

        }
        private static bool WriteDataRange2PHP(StreamWriter ws, string fontCode, string importCode, ExcelFile xlsFile, ref int total, int start = 0, int limit = 0)
        {
            TXlsNamedRange range = xlsFile.GetNamedRange("data_" + importCode, 0);

            if (range == null)
            {
                range = xlsFile.GetNamedRange("data", 0);
            }

            if (range != null)
            {
                xlsFile.ActiveSheet = range.SheetIndex;
                total = range.RowCount - 1;

                if (limit == 0)
                    limit = total;
                ws.Write(@"<?php
$xls2php = array(
");
                for (int r = 1; r <= limit; r++)
                {
                    if (r + start <= total)
                    {
                        if (r != 1) ws.Write(",\n");
                        ws.Write("  (Object)array(");
                        for (int c = 0; c < range.ColCount; c++)
                        {
                            if (c != 0) ws.Write(",");
                            string name = xlsFile.GetCellValue(range.Top, range.Left + c).ToString();
                            object v = GetValueObj(xlsFile, range.Top + r + start, range.Left + c, fontCode);
                            if (v == null)
                                v = "";
                            if (v is DateTime) ws.Write(string.Format("'{0}' => '{1:yyyy-MM-dd hh:mm:ss}'", name, v));
                            else if (v is double) ws.Write(string.Format("'{0}' => {1}", name, v));
                            else ws.Write(string.Format("'{0}' => '{1}'", name, v.ToString().Trim()));
                        }
                        ws.Write(")");
                    }
                }
                ws.Write(@"
);
?>");
                return true;
            }
            return false;
        }

        private static void InitAdvancePHP(ExcelFile xlsFile, List<TXlsNamedRange> arrRange, ref int maxCol, ref int maxRow, string importCode)
        {
            for (int i = 0; i < xlsFile.NamedRangeCount; i++)
            {
                string name = xlsFile.GetNamedRange(i + 1).Name;
                if (name.Length > 3 && (name.Substring(0, 3) == "d__" || name.Substring(0, 3) == "f__"))
                {
                    if (name.Contains(String.Format("f__{0}_", importCode)) || name.Contains(String.Format("d__{0}_", importCode)))
                    {
                        string colName = name.Replace(String.Format("f__{0}_", importCode), "").Replace(String.Format("d__{0}_", importCode), "");

                        TXlsNamedRange range = xlsFile.GetNamedRange(i + 1);
                        //if (maxCol < range.ColCount) maxCol = range.ColCount;
                        //if (maxRow < range.RowCount) maxRow = range.RowCount;

                        for (int j = 0; j < arrRange.Count; j++)
                        {
                            if (arrRange[j].Name == "f__" + colName || arrRange[j].Name == "d__" + colName)
                            {
                                arrRange.RemoveAt(j);
                                break;
                            }
                        }
                        if (name.Substring(0, 3) == "d__")
                        {
                            arrRange.Insert(0, range);
                        }
                        else
                        {
                            //if (maxCol < range.ColCount) maxCol = range.ColCount;
                            arrRange.Add(range);
                        }
                    }
                    else
                    {
                        TXlsNamedRange range = xlsFile.GetNamedRange(i + 1);
                        //if (maxCol < range.ColCount) maxCol = range.ColCount;
                        //if (maxRow < range.RowCount) maxRow = range.RowCount;
                        bool flag = true;
                        for (int j = 0; j < arrRange.Count; j++)
                        {
                            if (arrRange[j].Name == String.Format("f__{0}_{1}", importCode, name.Substring(3)) || arrRange[j].Name == String.Format("d__{0}_{1}", importCode, name.Substring(3)))
                            {
                                flag = false;
                                break;
                            }
                        }
                        if (flag)
                        {
                            if (name.Substring(0, 3) == "d__")
                            {
                                arrRange.Insert(0, range);
                            }
                            else
                            {
                                //if (maxCol < range.ColCount) maxCol = range.ColCount;
                                arrRange.Add(range);
                            }
                        }
                    }
                }

            }
            foreach (TXlsNamedRange x in arrRange)
            {
                if (maxCol < x.ColCount) maxCol = x.ColCount;
                if (maxRow < x.RowCount) maxRow = x.RowCount;
            }
        }
        private static bool WriteAdvance2PHP(StreamWriter ws, string fontCode, ExcelFile xlsFile, List<TXlsNamedRange> arrRange, int maxCol, int maxRow, string importCode, int start = 0, int limit = 0)
        {
            int total = maxRow * maxCol;
            if (limit == 0) limit = total;

            int i = start / maxRow;
            int j = start % maxRow;
            bool isBreak = false;
            ws.Write(@"<?php
$xls2php = array(
");
            for (; i < maxCol; i++)
            {
                if (isBreak) break;
                for (; j < maxRow; j++)
                {
                    if (i * maxRow + j > limit + start)
                    {
                        isBreak = true;
                        break;
                    }
                    int index = 0;
                    if (i == start / maxRow && j == start % maxRow) ws.Write(",\n");

                    ws.Write("  (Object)array(");

                    for (; index < arrRange.Count; index++)
                    {
                        if (index != 0) ws.Write(",");
                        xlsFile.ActiveSheet = arrRange[index].SheetIndex;
                        int top = Math.Min(j, arrRange[index].RowCount - 1) + arrRange[index].Top;
                        int left = Math.Min(i, arrRange[index].ColCount - 1) + arrRange[index].Left;
                        string name = GetFieldName(importCode, arrRange[index].Name);
                        if (index == 0)
                        {
                            int atop = top;
                            int aleft = left;
                            if (!xlsFile.CellMergedBounds(top, left).IsOneCell)
                            {
                                atop = xlsFile.CellMergedBounds(top, left).Top;
                                aleft = xlsFile.CellMergedBounds(top, left).Left;
                            }
                            object v = GetValueObj(xlsFile, top, left, fontCode);
                            if (v == null)
                                break;

                            if (v is DateTime) ws.Write(string.Format("'{0}' => '{1:yyyy-MM-dd hh:mm:ss}'", name, v));
                            else if (v is double) ws.Write(string.Format("'{0}' => {1}", name, v));
                            else ws.Write(string.Format("'{0}' => '{1}'", name, v.ToString().Trim()));
                        }
                        else
                        {
                            object v = GetValueObj(xlsFile, top, left, fontCode);
                            if (v == null)
                                v = "";
                            if (v is DateTime) ws.Write(string.Format("'{0}' => '{1:yyyy-MM-dd hh:mm:ss}'", name, v));
                            else if (v is double) ws.Write(string.Format("'{0}' => {1}", name, v));
                            else ws.Write(string.Format("'{0}' => '{1}'", name, v.ToString().Trim()));
                        }

                    }
                    ws.Write(")");
                }
            }
            ws.Write(@"
);
?>");
            return true;
        }

        #endregion Excel to PHP

        #region Excel to CSV

        public static bool Excel2CSV(string xlsFileName, string phpFileName, string fontCode, string importCode, ref int total, int start = 0, int limit = 0)
        {
            bool ret = false;
            if (File.Exists(xlsFileName))
            {
                if (File.Exists(phpFileName)) File.Delete(phpFileName);

                XlsFile xlsFile = new XlsFile(xlsFileName);
                using (FileStream fs = new FileStream(phpFileName, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite))
                {
                    using (StreamWriter ws = new StreamWriter(fs))
                    {
                        ret = WriteDataRange2CSV(ws, fontCode, importCode, xlsFile, ref total, start, limit);
                        if (ret == false)
                        {
                            List<TXlsNamedRange> arrRange = new List<TXlsNamedRange>();
                            int maxCol = 1;
                            int maxRow = 1;
                            InitAdvanceCSV(xlsFile, arrRange, ref maxCol, ref maxRow, importCode);
                            total = maxCol * maxRow;
                            if (arrRange.Count == 0)
                                ret = false;
                            ret = WriteAdvance2CSV(ws, fontCode, xlsFile, arrRange, maxCol, maxRow, importCode, start, limit);
                        }
                    }
                }

            }
            return ret;

        }
        public static bool Excel2Xml(ExcelFile xlsFile, string xmlFileName, string fontCode, string importCode, ref int total, int start = 0, int limit = 0)
        {
            bool ret = false;
            using (FileStream fs = new FileStream(xmlFileName, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite))
            {
                using (StreamWriter ws = new StreamWriter(fs))
                {
                    ret = WriteDataRange2Xml(ws, fontCode, importCode, xlsFile, ref total, start, limit);
                    if (ret == false)
                    {
                        List<TXlsNamedRange> arrRange = new List<TXlsNamedRange>();
                        int maxCol = 1;
                        int maxRow = 1;
                        InitAdvanceXml(xlsFile, arrRange, ref maxCol, ref maxRow, importCode);
                        total = maxCol * maxRow;
                        if (arrRange.Count == 0)
                            ret = false;
                        ret = WriteAdvance2Xml(ws, fontCode, xlsFile, arrRange, maxCol, maxRow, importCode, start, limit);
                    }
                }
            }


            return ret;

        }

        public static bool Excel2CSV(ExcelFile xlsFile, string phpFileName, string fontCode, string importCode, ref int total, int start = 0, int limit = 0)
        {
            bool ret = false;

            using (FileStream fs = new FileStream(phpFileName, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite))
            {
                using (StreamWriter ws = new StreamWriter(fs))
                {
                    ret = WriteDataRange2CSV(ws, fontCode, importCode, xlsFile, ref total, start, limit);
                    if (ret == false)
                    {
                        List<TXlsNamedRange> arrRange = new List<TXlsNamedRange>();
                        int maxCol = 1;
                        int maxRow = 1;
                        InitAdvanceCSV(xlsFile, arrRange, ref maxCol, ref maxRow, importCode);
                        total = maxCol * maxRow;
                        if (arrRange.Count == 0)
                            ret = false;
                        ret = WriteAdvance2CSV(ws, fontCode, xlsFile, arrRange, maxCol, maxRow, importCode, start, limit);
                    }
                }
            }

            return ret;

        }
        private static bool WriteDataRange2CSV(StreamWriter ws, string fontCode, string importCode, ExcelFile xlsFile, ref int total, int start = 0, int limit = 0)
        {
            TXlsNamedRange range = xlsFile.GetNamedRange("data_" + importCode, 0);

            if (range == null)
            {
                range = xlsFile.GetNamedRange("data", 0);
            }

            if (range != null)
            {
                xlsFile.ActiveSheet = range.SheetIndex;
                total = range.RowCount - 1;

                if (limit == 0)
                    limit = total;

                for (int c = 0; c < range.ColCount; c++)
                {
                    if (c != 0) ws.Write("|");
                    ws.Write(xlsFile.GetCellValue(range.Top, range.Left + c).ToString());
                }
                ws.Write("\n");
                for (int r = 1; r <= limit; r++)
                {
                    if (r + start <= total)
                    {
                        if (r != 1) ws.Write("\n");

                        for (int c = 0; c < range.ColCount; c++)
                        {
                            if (c != 0) ws.Write("|");
                            object v = GetValueObj(xlsFile, range.Top + r + start, range.Left + c, fontCode);
                            if (v == null)
                                v = "";
                            if (v is DateTime) ws.Write(string.Format("{0:yyyy-MM-dd hh:mm:ss}", v));
                            else if (v is double) ws.Write(string.Format("{0}", v));
                            else ws.Write(string.Format("{0}", v.ToString().Trim()));
                        }

                    }
                }
                return true;
            }
            return false;
        }

        private static void InitAdvanceCSV(ExcelFile xlsFile, List<TXlsNamedRange> arrRange, ref int maxCol, ref int maxRow, string importCode)
        {
            for (int i = 0; i < xlsFile.NamedRangeCount; i++)
            {
                string name = xlsFile.GetNamedRange(i + 1).Name;
                if (name.Length > 3 && (name.Substring(0, 3) == "d__" || name.Substring(0, 3) == "f__"))
                {
                    if (name.Contains(String.Format("f__{0}_", importCode)) || name.Contains(String.Format("d__{0}_", importCode)))
                    {
                        string colName = name.Replace(String.Format("f__{0}_", importCode), "").Replace(String.Format("d__{0}_", importCode), "");

                        TXlsNamedRange range = xlsFile.GetNamedRange(i + 1);
                        //if (maxCol < range.ColCount) maxCol = range.ColCount;
                        //if (maxRow < range.RowCount) maxRow = range.RowCount;

                        for (int j = 0; j < arrRange.Count; j++)
                        {
                            if (arrRange[j].Name == "f__" + colName || arrRange[j].Name == "d__" + colName)
                            {
                                arrRange.RemoveAt(j);
                                break;
                            }
                        }
                        if (name.Substring(0, 3) == "d__")
                        {
                            arrRange.Insert(0, range);
                        }
                        else
                        {
                            //if (maxCol < range.ColCount) maxCol = range.ColCount;
                            arrRange.Add(range);
                        }
                    }
                    else
                    {
                        TXlsNamedRange range = xlsFile.GetNamedRange(i + 1);
                        //if (maxCol < range.ColCount) maxCol = range.ColCount;
                        //if (maxRow < range.RowCount) maxRow = range.RowCount;
                        bool flag = true;
                        for (int j = 0; j < arrRange.Count; j++)
                        {
                            if (arrRange[j].Name == String.Format("f__{0}_{1}", importCode, name.Substring(3)) || arrRange[j].Name == String.Format("d__{0}_{1}", importCode, name.Substring(3)))
                            {
                                flag = false;
                                break;
                            }
                        }
                        if (flag)
                        {
                            if (name.Substring(0, 3) == "d__")
                            {
                                arrRange.Insert(0, range);
                            }
                            else
                            {
                                //if (maxCol < range.ColCount) maxCol = range.ColCount;
                                arrRange.Add(range);
                            }
                        }
                    }
                }

            }
            foreach (TXlsNamedRange x in arrRange)
            {
                if (maxCol < x.ColCount) maxCol = x.ColCount;
                if (maxRow < x.RowCount) maxRow = x.RowCount;
            }
        }
        private static bool WriteAdvance2CSV(StreamWriter ws, string fontCode, ExcelFile xlsFile, List<TXlsNamedRange> arrRange, int maxCol, int maxRow, string importCode, int start = 0, int limit = 0)
        {
            int total = maxRow * maxCol;
            if (limit == 0) limit = total;

            int i = start / maxRow;
            int j = start % maxRow;
            bool isBreak = false;

            for (int index = 0; index < arrRange.Count; index++)
            {
                if (index != 0) ws.Write("|");
                xlsFile.ActiveSheet = arrRange[index].SheetIndex;
                ws.Write(GetFieldName(importCode, arrRange[index].Name));
            }
            ws.Write("\n");
            for (; i < maxCol; i++)
            {
                if (isBreak) break;
                for (; j < maxRow; j++)
                {
                    if (i * maxRow + j > limit + start)
                    {
                        isBreak = true;
                        break;
                    }
                    if (i == start / maxRow && j == start % maxRow) ws.Write("\n");

                    for (int index = 0; index < arrRange.Count; index++)
                    {
                        if (index != 0) ws.Write("|");
                        xlsFile.ActiveSheet = arrRange[index].SheetIndex;
                        int top = Math.Min(j, arrRange[index].RowCount - 1) + arrRange[index].Top;
                        int left = Math.Min(i, arrRange[index].ColCount - 1) + arrRange[index].Left;
                        string name = GetFieldName(importCode, arrRange[index].Name);
                        if (index == 0)
                        {
                            int atop = top;
                            int aleft = left;
                            if (!xlsFile.CellMergedBounds(top, left).IsOneCell)
                            {
                                atop = xlsFile.CellMergedBounds(top, left).Top;
                                aleft = xlsFile.CellMergedBounds(top, left).Left;
                            }
                            object v = GetValueObj(xlsFile, top, left, fontCode);
                            if (v == null)
                                break;

                            if (v is DateTime) ws.Write(string.Format("{0:yyyy-MM-dd hh:mm:ss}", v));
                            else if (v is double) ws.Write(string.Format("{0}", v));
                            else ws.Write(string.Format("{0}", v.ToString().Trim()));
                        }
                        else
                        {
                            object v = GetValueObj(xlsFile, top, left, fontCode);
                            if (v == null)
                                v = "";
                            if (v is DateTime) ws.Write(string.Format("{0:yyyy-MM-dd hh:mm:ss}", v));
                            else if (v is double) ws.Write(string.Format("{0}", v));
                            else ws.Write(string.Format("{0}", v.ToString().Trim()));
                        }

                    }
                }
            }
            return true;
        }

        #endregion Excel to CSV

        #region Excel to Array Format

        public static object[][] Excel2MyArray(string xlsFileName, string fontCode, string importCode, ref int total, int start = 0, int limit = 0)
        {
            if (File.Exists(xlsFileName))
            {

                XlsFile xlsFile = new XlsFile(xlsFileName);

                object[][] myArray = WriteDataRange2MyArray(fontCode, importCode, xlsFile, ref total, start, limit);
                if (myArray == null)
                {
                    List<TXlsNamedRange> arrRange = new List<TXlsNamedRange>();
                    int maxCol = 1;
                    int maxRow = 1;
                    InitAdvanceMyArray(xlsFile, arrRange, ref maxCol, ref maxRow, importCode);
                    total = maxCol * maxRow;
                    if (arrRange.Count == 0)
                        return null;
                    return WriteAdvance2MyArray(fontCode, xlsFile, arrRange, maxCol, maxRow, importCode, start, limit);
                }
                return myArray;

            }
            return null;

        }
        private static object[][] WriteDataRange2MyArray(string fontCode, string importCode, ExcelFile xlsFile, ref int total, int start = 0, int limit = 0)
        {
            TXlsNamedRange range = xlsFile.GetNamedRange("data_" + importCode, 0);

            if (range == null)
            {
                range = xlsFile.GetNamedRange("data", 0);
            }

            if (range != null)
            {
                xlsFile.ActiveSheet = range.SheetIndex;
                total = range.RowCount - 1;

                if (limit == 0)
                    limit = total;

                object[][] myArray = new object[limit + 1][];
                myArray[0] = new string[range.ColCount];

                for (int c = 0; c < range.ColCount; c++)
                    myArray[0][c] = xlsFile.GetCellValue(range.Top, range.Left + c).ToString();

                for (int r = 1; r <= limit; r++)
                {
                    if (r + start <= total)
                    {
                        myArray[r] = new object[range.ColCount];
                        for (int c = 0; c < range.ColCount; c++)
                        {
                            object v = GetValueObj(xlsFile, range.Top + r + start, range.Left + c, fontCode);
                            if (v == null)
                                v = "";
                            myArray[r][c] = v;
                        }

                    }
                }
                return myArray;
            }
            return null;
        }

        private static void InitAdvanceMyArray(ExcelFile xlsFile, List<TXlsNamedRange> arrRange, ref int maxCol, ref int maxRow, string importCode)
        {
            for (int i = 0; i < xlsFile.NamedRangeCount; i++)
            {
                string name = xlsFile.GetNamedRange(i + 1).Name;
                if (name.Length > 3 && (name.Substring(0, 3) == "d__" || name.Substring(0, 3) == "f__"))
                {
                    if (name.Contains(String.Format("f__{0}_", importCode)) || name.Contains(String.Format("d__{0}_", importCode)))
                    {
                        string colName = name.Replace(String.Format("f__{0}_", importCode), "").Replace(String.Format("d__{0}_", importCode), "");

                        TXlsNamedRange range = xlsFile.GetNamedRange(i + 1);
                        //if (maxCol < range.ColCount) maxCol = range.ColCount;
                        //if (maxRow < range.RowCount) maxRow = range.RowCount;

                        for (int j = 0; j < arrRange.Count; j++)
                        {
                            if (arrRange[j].Name == "f__" + colName || arrRange[j].Name == "d__" + colName)
                            {
                                arrRange.RemoveAt(j);
                                break;
                            }
                        }
                        if (name.Substring(0, 3) == "d__")
                        {
                            arrRange.Insert(0, range);
                        }
                        else
                        {
                            //if (maxCol < range.ColCount) maxCol = range.ColCount;
                            arrRange.Add(range);
                        }
                    }
                    else
                    {
                        TXlsNamedRange range = xlsFile.GetNamedRange(i + 1);
                        //if (maxCol < range.ColCount) maxCol = range.ColCount;
                        //if (maxRow < range.RowCount) maxRow = range.RowCount;
                        bool flag = true;
                        for (int j = 0; j < arrRange.Count; j++)
                        {
                            if (arrRange[j].Name == String.Format("f__{0}_{1}", importCode, name.Substring(3)) || arrRange[j].Name == String.Format("d__{0}_{1}", importCode, name.Substring(3)))
                            {
                                flag = false;
                                break;
                            }
                        }
                        if (flag)
                        {
                            if (name.Substring(0, 3) == "d__")
                            {
                                arrRange.Insert(0, range);
                            }
                            else
                            {
                                //if (maxCol < range.ColCount) maxCol = range.ColCount;
                                arrRange.Add(range);
                            }
                        }
                    }
                }

            }
            foreach (TXlsNamedRange x in arrRange)
            {
                if (maxCol < x.ColCount) maxCol = x.ColCount;
                if (maxRow < x.RowCount) maxRow = x.RowCount;
            }
        }
        private static object[][] WriteAdvance2MyArray(string fontCode, ExcelFile xlsFile, List<TXlsNamedRange> arrRange, int maxCol, int maxRow, string importCode, int start = 0, int limit = 0)
        {
            int total = maxRow * maxCol;
            if (limit == 0) limit = total;

            object[][] myArray = new object[limit + 1][];
            myArray[0] = new string[arrRange.Count];

            int i = start / maxRow;
            int j = start % maxRow;
            bool isBreak = false;

            for (int index = 0; index < arrRange.Count; index++)
            {
                xlsFile.ActiveSheet = arrRange[index].SheetIndex;
                myArray[0][index] = GetFieldName(importCode, arrRange[index].Name);
            }

            for (; i < maxCol; i++)
            {
                if (isBreak) break;
                for (; j < maxRow; j++)
                {
                    if (i * maxRow + j > limit + start)
                    {
                        isBreak = true;
                        break;
                    }
                    //if (i == start / maxRow && j == start % maxRow) ws.Write("\n");
                    myArray[i * maxRow + j] = new object[arrRange.Count];

                    for (int index = 0; index < arrRange.Count; index++)
                    {
                        xlsFile.ActiveSheet = arrRange[index].SheetIndex;
                        int top = Math.Min(j, arrRange[index].RowCount - 1) + arrRange[index].Top;
                        int left = Math.Min(i, arrRange[index].ColCount - 1) + arrRange[index].Left;
                        int atop = top;
                        int aleft = left;
                        if (!xlsFile.CellMergedBounds(top, left).IsOneCell)
                        {
                            atop = xlsFile.CellMergedBounds(top, left).Top;
                            aleft = xlsFile.CellMergedBounds(top, left).Left;
                        }
                        object v = "";
                        if (index == 0)
                        {
                            v = GetValueObj(xlsFile, atop, aleft, fontCode);
                            if (v == null)
                                break;
                        }
                        myArray[i * maxRow + j][index] = v;

                    }
                }
            }
            return myArray;
        }

        #endregion Excel to CSV

        private static void GetDataRange(DataTable dt, string fontCode, string importCode, ExcelFile xlsFile, ref int total, int start = 0, int limit = 0)
        {
            TXlsNamedRange range = xlsFile.GetNamedRange("data_" + importCode, 0);

            if (range == null)
            {
                range = xlsFile.GetNamedRange("data", 0);
            }



            if (range != null)
            {
                xlsFile.ActiveSheet = range.SheetIndex;
                total = range.RowCount;

                if (limit == 0)
                    limit = total;

                for (int r = 1; r < limit; r++)
                {
                    DataRow newRow = dt.NewRow();
                    if (r + start < total)
                    {
                        for (int c = 0; c < range.ColCount; c++)
                        {
                            if (xlsFile.GetCellValue(range.Top, range.Left + c) != null && dt.Columns.Contains(xlsFile.GetCellValue(range.Top, range.Left + c).ToString()))
                            {
                                GetValue(ref newRow, xlsFile.GetCellValue(range.Top, range.Left + c).ToString(), xlsFile, range.Top + r + start, range.Left + c, fontCode);
                            }
                            else if (xlsFile.GetCellValue(range.Top, range.Left + c) != null)
                            {
                                dt.Columns.Add(xlsFile.GetCellValue(range.Top, range.Left + c).ToString());
                                GetValue(ref newRow, xlsFile.GetCellValue(range.Top, range.Left + c).ToString(), xlsFile, range.Top + r + start, range.Left + c, fontCode);
                            }
                        }
                        dt.Rows.Add(newRow);
                    }
                }

            }
        }

        public static DataSet PreviewExcelFile(DataSet ds, string filename, string fontCode)
        {
            if (File.Exists(filename))
            {
                XlsFile xlsFile = new XlsFile(filename, true);
                ds = PreviewExcelFile(ds, xlsFile, fontCode);
            }
            return ds;
        }

        public static DataSet PreviewExcelFile(DataSet ds, ExcelFile xlsFile, string fontCode)
        {
            foreach (DataTable dt in ds.Tables)
            {
                //DataTable dt = new DataTable();
                dt.PrimaryKey = null;
                foreach (DataColumn col in dt.Columns)
                {
                    col.AllowDBNull = true;
                }

                List<TXlsNamedRange> arrRange = new List<TXlsNamedRange>();

                int maxCol = 1;
                int maxRow = 1;
                int total = 0;
                GetDataRange(dt, fontCode, dt.TableName, xlsFile, ref total);
                if (dt.Rows.Count > 0) continue;
                InitAdvance(dt, xlsFile, arrRange, ref maxCol, ref maxRow);
                if (arrRange.Count == 0) continue;
                GetAdvance(dt, fontCode, xlsFile, arrRange, maxCol, maxRow);
            }
            DataTable dtRel = null;
            if (!ds.Tables.Contains("RELATIONSHIP"))
            {
                dtRel = new DataTable("RELATIONSHIP");
                dtRel.Columns.AddRange(new DataColumn[] { new DataColumn("Name"), new DataColumn("Source") });
                dtRel.PrimaryKey = new DataColumn[] { dtRel.Columns["Name"] };
                ds.Tables.Add(dtRel);
            }
            else
            {
                dtRel = ds.Tables["RELATIONSHIP"];
            }
            int to = 0;
            GetDataRange(dtRel, fontCode, dtRel.TableName, xlsFile, ref to);
            if (dtRel.Rows.Count > 0)
            {
                foreach (DataRow r in dtRel.Rows)
                {
                    string[] relations;
                    string parent, child;
                    string tmp = r["Name"].ToString();
                    parent = tmp.Substring(0, tmp.IndexOf("->"));
                    child = tmp.Replace(parent + "->", "");
                    relations = r["Source"].ToString().Split(';');

                    if (ds.Tables.Contains(parent) && ds.Tables.Contains(child))
                    {
                        ds.Tables[parent].Columns.Add(child);
                        foreach (DataRow rParent in ds.Tables[parent].Rows)
                        {
                            string filter = "";
                            for (int i = 0; i < relations.Length; i++)
                            {
                                string parentKey, childKey;

                                parentKey = relations[i].Substring(0, relations[i].IndexOf("->"));
                                childKey = relations[i].Replace(parentKey + "->", "");
                                if (i > 0) filter += " AND ";
                                filter += string.Format("{0} = '{1}'", childKey, rParent[parentKey]);
                            }
                            DataRow[] rows = ds.Tables[child].Select(filter);
                            object[] objs = new object[rows.Length];
                            for (int j = 0; j < rows.Length; j++)
                            {
                                objs[j] = rows[j].ItemArray;
                                ds.Tables[child].Rows.Remove(rows[j]);
                            }

                            rParent[child] = objs;
                        }

                    }
                }
            }

            return ds;
        }

        public static DataTable PreviewExcelFile(DataTable dt, ExcelFile xlsFile, string fontCode, string importCode, ref int total)
        {
            //DataTable dt = new DataTable();
            dt.PrimaryKey = null;
            foreach (DataColumn col in dt.Columns)
            {
                col.AllowDBNull = true;
            }

            List<TXlsNamedRange> arrRange = new List<TXlsNamedRange>();

            int maxCol = 1;
            int maxRow = 1;
            GetDataRange(dt, fontCode, importCode, xlsFile, ref total);


            if (dt.Rows.Count > 0)
                return dt;
            InitAdvance(dt, xlsFile, arrRange, ref maxCol, ref maxRow);
            if (arrRange.Count == 0)
                return dt;
            GetAdvance(dt, fontCode, xlsFile, arrRange, maxCol, maxRow);

            return dt;
        }

        private static void GetAdvance(DataTable dt, string fontCode, ExcelFile xlsFile, List<TXlsNamedRange> arrRange, int maxCol, int maxRow, int start = 0, int limit = 0)
        {
            int total = maxRow * maxCol;
            if (limit == 0) limit = total;

            int i = start / maxRow;
            int j = start % maxRow;
            bool isBreak = false;
            for (; i < maxCol; i++)
            {
                if (isBreak) break;
                for (; j < maxRow; j++)
                {
                    if (i * maxRow + j > limit + start)
                    {
                        isBreak = true;
                        break;
                    }

                    DataRow newRow = dt.NewRow();
                    bool flag = true;
                    int index = 0;
                    for (; index < arrRange.Count; index++)
                    {
                        xlsFile.ActiveSheet = arrRange[index].SheetIndex;
                        int top = Math.Min(j, arrRange[index].RowCount - 1) + arrRange[index].Top;
                        int left = Math.Min(i, arrRange[index].ColCount - 1) + arrRange[index].Left;
                        if (index == 0)
                        {
                            int atop = top;
                            int aleft = left;
                            if (!xlsFile.CellMergedBounds(top, left).IsOneCell)
                            {
                                atop = xlsFile.CellMergedBounds(top, left).Top;
                                aleft = xlsFile.CellMergedBounds(top, left).Left;
                            }
                            object dObject = GetObject(xlsFile.GetCellValue(atop, aleft), fontCode);
                            if (dObject == null || dObject.ToString() == "" || dObject == DBNull.Value)
                            {
                                flag = false;
                                break;
                            }
                            else
                            {
                                GetValue(ref newRow, GetFieldName(dt, arrRange[index].Name), xlsFile, top, left, fontCode);
                            }
                        }
                        else
                        {
                            GetValue(ref newRow, GetFieldName(dt, arrRange[index].Name), xlsFile, top, left, fontCode);
                        }

                    }
                    if (flag)
                    {
                        dt.Rows.Add(newRow);
                    }
                }
            }
        }

        private static string GetFieldName(DataTable dt, string p)
        {
            return GetFieldName(dt.TableName, p);
        }
        private static string GetFieldName(string importCode, string p)
        {
            return p.Replace(String.Format("f__{0}_", importCode), "").Replace(String.Format("d__{0}_", importCode), "").Replace("f__", "").Replace("d__", "");
        }

        private static void InitAdvance(DataTable dt, ExcelFile xlsFile, List<TXlsNamedRange> arrRange, ref int maxCol, ref int maxRow)
        {
            for (int i = 0; i < xlsFile.NamedRangeCount; i++)
            {
                string name = xlsFile.GetNamedRange(i + 1).Name;
                if (name.Length > 3 && (name.Substring(0, 3) == "d__" || name.Substring(0, 3) == "f__"))
                {
                    if (name.Contains(String.Format("f__{0}_", dt.TableName)) || name.Contains(String.Format("d__{0}_", dt.TableName)))
                    {
                        string colName = name.Replace(String.Format("f__{0}_", dt.TableName), "").Replace(String.Format("d__{0}_", dt.TableName), "");
                        if (!dt.Columns.Contains(colName))
                        {
                            dt.Columns.Add(colName);
                        }
                        TXlsNamedRange range = xlsFile.GetNamedRange(i + 1);
                        //if (maxCol < range.ColCount) maxCol = range.ColCount;
                        //if (maxRow < range.RowCount) maxRow = range.RowCount;

                        for (int j = 0; j < arrRange.Count; j++)
                        {
                            if (arrRange[j].Name == "f__" + colName || arrRange[j].Name == "d__" + colName)
                            {
                                arrRange.RemoveAt(j);
                                break;
                            }
                        }
                        if (name.Substring(0, 3) == "d__")
                        {
                            arrRange.Insert(0, range);
                        }
                        else
                        {
                            //if (maxCol < range.ColCount) maxCol = range.ColCount;
                            arrRange.Add(range);
                        }
                    }
                    else if (dt.Columns.Contains(name.Substring(3)))
                    {
                        TXlsNamedRange range = xlsFile.GetNamedRange(i + 1);
                        //if (maxCol < range.ColCount) maxCol = range.ColCount;
                        //if (maxRow < range.RowCount) maxRow = range.RowCount;
                        bool flag = true;
                        for (int j = 0; j < arrRange.Count; j++)
                        {
                            if (arrRange[j].Name == String.Format("f__{0}_{1}", dt.TableName, name.Substring(3)) || arrRange[j].Name == String.Format("d__{0}_{1}", dt.TableName, name.Substring(3)))
                            {
                                flag = false;
                                break;
                            }
                        }
                        if (flag)
                        {
                            if (name.Substring(0, 3) == "d__")
                            {
                                arrRange.Insert(0, range);
                            }
                            else
                            {
                                //if (maxCol < range.ColCount) maxCol = range.ColCount;
                                arrRange.Add(range);
                            }
                        }
                    }
                    else
                    {
                        dt.Columns.Add(name.Substring(3));
                        TXlsNamedRange range = xlsFile.GetNamedRange(i + 1);
                        //if (maxCol < range.ColCount) maxCol = range.ColCount;
                        //if (maxRow < range.RowCount) maxRow = range.RowCount;
                        bool flag = true;
                        for (int j = 0; j < arrRange.Count; j++)
                        {
                            if (arrRange[j].Name == String.Format("f__{0}_{1}", dt.TableName, name.Substring(3)) || arrRange[j].Name == String.Format("d__{0}_{1}", dt.TableName, name.Substring(3)))
                            {
                                flag = false;
                                break;
                            }
                        }
                        if (flag)
                        {
                            if (name.Substring(0, 3) == "d__")
                            {
                                arrRange.Insert(0, range);
                            }
                            else
                            {
                                //if (maxCol < range.ColCount) maxCol = range.ColCount;
                                arrRange.Add(range);
                            }
                        }
                    }
                }

            }
            foreach (TXlsNamedRange x in arrRange)
            {
                if (maxCol < x.ColCount) maxCol = x.ColCount;
                if (maxRow < x.RowCount) maxRow = x.RowCount;
            }
        }

        private static void GetValue(ref DataRow newRow, string fieldName, ExcelFile xlsFile, int top, int left, string fontCode)
        {
            int atop = top;
            int aleft = left;
            try
            {
                if (!xlsFile.CellMergedBounds(atop, aleft).IsOneCell)
                {
                    atop = xlsFile.CellMergedBounds(atop, aleft).Top;
                    aleft = xlsFile.CellMergedBounds(atop, aleft).Left;
                }
                if (newRow.Table.Columns[fieldName].DataType == typeof(DateTime))
                    newRow[fieldName] = GetDateTimeObj(xlsFile, atop, aleft);
                else
                    newRow[fieldName] = GetObject(xlsFile.GetCellValue(atop, aleft), fontCode);
            }
            catch (Exception ex)
            {
                throw new Exception(String.Format(@"Erro {0}!R{1}C{2} : {3}", xlsFile.ActiveSheetByName, top, left, ex.Message));
            }
        }
        public static object GetValueObj(ExcelFile xlsFile, int top, int left, string fontCode)
        {
            int atop = top;
            int aleft = left;
            try
            {
                if (!xlsFile.CellMergedBounds(atop, aleft).IsOneCell)
                {
                    atop = xlsFile.CellMergedBounds(atop, aleft).Top;
                    aleft = xlsFile.CellMergedBounds(atop, aleft).Left;
                }
                int xf = 0;
                object v = xlsFile.GetCellValue(atop, aleft, ref xf);
                if (v == null)
                    return v;
                switch (Type.GetTypeCode(v.GetType()))
                {
                    case TypeCode.Boolean:
                        return (bool)v;

                    case TypeCode.Double:  //Remember, dates are doubles with date format.
                        TUIColor CellColor = Color.Empty;
                        bool HasDate, HasTime;
                        String CellValue = TFlxNumberFormat.FormatValue(v, xlsFile.GetFormat(xf).Format, ref CellColor, xlsFile, out HasDate, out HasTime).ToString();

                        if (HasDate || HasTime)
                        {
                            return FlxDateTime.FromOADate((double)v, xlsFile.OptionsDates1904);
                        }
                        else
                        {
                            return (double)v;
                        }
                    case TypeCode.String:
                        return v.ToString();

                }
                TFormula Fmla = v as TFormula;
                if (Fmla != null)
                {
                    if (Type.GetTypeCode(Fmla.Result.GetType()) == TypeCode.Double) return (double)Fmla.Result;
                    else if (Type.GetTypeCode(Fmla.Result.GetType()) == TypeCode.Boolean) return (bool)Fmla.Result;
                    else Fmla.Result.ToString();
                }

                return null;
            }
            catch (Exception ex)
            {
                throw new Exception(String.Format(@"Erro {0}!R{1}C{2} : {3}", xlsFile.ActiveSheetByName, top, left, ex.Message));
            }
        }
        private static object GetDateTimeObj(ExcelFile xlsFile, int top, int left)
        {
            string format = xlsFile.GetCellVisibleFormatDef(top, left).Format;
            if (format != "")
            {
                Object val = xlsFile.GetCellValue(top, left);
                if (val == null || xlsFile.GetCellValue(top, left).ToString() == "" || val.ToString() == "NULL") return DBNull.Value;

                if (val is double) return FlxDateTime.FromOADate(Convert.ToDouble(val), false);
                format = format.Replace("m", "M");
                return DateTime.ParseExact(val.ToString(), format, CultureInfo.InvariantCulture);

            }
            else
            {
                Object obj = xlsFile.GetCellValue(top, left);
                if (obj is double)
                {
                    int sunDate = Convert.ToInt32(obj);
                    int year = sunDate / 10000;
                    int month = (sunDate - year * 10000) / 100;
                    int day = sunDate - year * 10000 - month * 100;
                    return new DateTime(year, month, day);
                }
                else
                    return DBNull.Value;
            }
        }

        private static object GetObject(object p, string fontCode)
        {
            //object p = null;
            if (p == null || p.ToString().Trim().TrimStart() == "")
                return DBNull.Value;
            if (p is String)
            {
                if (fontCode == "" || fontCode == "None" || fontCode == "Unicode")
                {
                    //if (p is string)
                    return p.ToString().Trim();
                    //return p;
                }
                else
                {

                    if (fontCode == "TVCN3")
                        return VNConvertor.ConvertTCVN3ToUnicode(p.ToString().Trim().TrimStart());
                    else if (fontCode == "VNI")
                        return VNConvertor.ConvertVNI2Unicode(p.ToString().Trim().TrimStart());
                    //else 
                    //    return 
                }

            }
            else if (p is TFormula)
            {
                TFormula f = p as TFormula;
                if (f.Result is String)
                {
                    if (fontCode == "" || fontCode == "None" || fontCode == "Unicode")
                    {
                        //if (p is string)
                        return p.ToString().Trim();
                        //return p;
                    }
                    else
                    {

                        if (fontCode == "TVCN3")
                            return VNConvertor.ConvertTCVN3ToUnicode(p.ToString().Trim().TrimStart());
                        else if (fontCode == "VNI")
                            return VNConvertor.ConvertVNI2Unicode(p.ToString().Trim().TrimStart());
                        //else 
                        //    return 
                    }

                }
                return f.Result;
            }
            return p;
        }
       
    }
}
